package com.xb.system.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xb.model.domain.CompanyData;
import com.xb.model.domain.entity.FileUpload;
import com.xb.model.domain.entity.VipPackage;
import com.xb.system.config.RabbitMQConfig;
import com.xb.system.listener.ExcelDataThreadPoolListener;
import com.xb.system.mapper.db1.FileUploadMapper;
import com.xb.system.mapper.db1.VipPackageMapper;
import com.xb.system.service.IOExcelService;
import com.xb.system.service.UsersService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.amqp.rabbit.annotation.RabbitListener;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.UUID;
import java.util.concurrent.atomic.AtomicInteger;
import java.io.File;
import java.nio.file.Files;
import java.util.List;
import java.util.regex.Pattern;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

@Service
@Slf4j
public class IOExcelServiceImpl implements IOExcelService {

    // 最大允许上传的文件数量
    private static final int MAX_PENDING_FILES = 10;

    @Autowired
    private LocalFileStorageService fileStorageService;
    @Autowired
    private FileUploadMapper fileUploadMapper;
    @Autowired
    private VipPackageMapper vipPackageMapper;
    @Autowired
    private UsersService userService;
    @Autowired
    private RabbitTemplate rabbitTemplate;
    @Value("${file.output.path}")
    private String resultOutputDir;

    @Override
    public String processExcelFile(MultipartFile file, Long userId, int maxRecords) throws IOException {
        log.info("开始处理文件，最大允许记录数: {}", maxRecords);
        VipPackage vipPackage = vipPackageMapper.selectOne(new QueryWrapper<VipPackage>()
                .eq("user_id", userId).eq("is_deleted", 0));
        if (vipPackage == null || vipPackage.getTotalQuota() - vipPackage.getUsedQuota() <= 0) {
            throw new IllegalStateException("用户余额不足");
        }

        // 检查待处理的文件数量，限制最多10个
        long pendingCount = fileUploadMapper.selectCount(new QueryWrapper<FileUpload>()
                .eq("user_id", userId)
                .eq("is_deleted", 0)
                .in("status", "未完成", "处理中"));
        
        if (pendingCount >= MAX_PENDING_FILES) {
            throw new IllegalStateException("待处理文件数量已达上限（" + MAX_PENDING_FILES + "个），请等待部分文件处理完成后再上传");
        }

        // 先上传文件，以便后续处理
        String originalPath = fileStorageService.uploadFile(file, "original");
        FileUpload fileUpload = new FileUpload();
        fileUpload.setUserId(userId);
        fileUpload.setFileName(file.getOriginalFilename());
        fileUpload.setStatus("未完成");
        fileUpload.setOriginalFilePath(originalPath);
        fileUpload.setUploadTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
        fileUpload.setCreateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
        fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
        fileUpload.setIsDeleted(0);

        try {
            // 检查文件中的数据行数（不是号码数量）
            int totalDataRows = calculateDataRowCount(fileStorageService.getFileStream(originalPath));
            log.info("文件中包含数据行数: {}, 最大允许记录数: {}", totalDataRows, maxRecords);
            
            // 设置文件中的总记录数（数据行数）
            fileUpload.setTotalCount(totalDataRows);
            
            // 检查数据行数是否超过限制
            if (totalDataRows > maxRecords) {
                fileUpload.setStatus("格式错误");
                fileUpload.setErrorMessage("数据行数 " + totalDataRows + " 超过最大允许数 " + maxRecords);
                fileUploadMapper.insert(fileUpload);
                throw new IllegalStateException("文件中的数据行数(" + totalDataRows + ")超过限制(" + maxRecords + ")");
            }
            
            // 验证法定代表人字段
            validateLegalRepresentative(fileStorageService.getFileStream(originalPath), totalDataRows);
            
            // 保存文件记录
            fileUploadMapper.insert(fileUpload);
            
            // 如果当前没有正在处理的文件，则直接发送到队列
            // 如果有正在处理的文件，则新上传的文件会在队列中等待
            if (pendingCount == 0) {
                // 使用与findAndProcessNextFile方法一致的消息发送方式
                rabbitTemplate.convertAndSend(RabbitMQConfig.FILE_EXCHANGE, 
                        RabbitMQConfig.FILE_ROUTING_KEY, fileUpload.getId());
                log.info("已将文件ID: {}发送到处理队列", fileUpload.getId());
            }
            
            return fileUpload.getOriginalFilePath();
        } catch (Exception e) {
            fileUpload.setStatus("格式错误");
            fileUpload.setErrorMessage(e.getMessage());
            fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
            fileUploadMapper.insert(fileUpload);
            throw e;
        }
    }

    @RabbitListener(queues = RabbitMQConfig.FILE_QUEUE)
    @Transactional(rollbackFor = Exception.class)
    public void processFile(Long fileUploadId) {
        log.info("开始处理文件，ID: {}", fileUploadId);
        
        // 先查询文件状态
        FileUpload fileUpload = fileUploadMapper.selectById(fileUploadId);
        if (fileUpload == null) {
            log.error("未找到ID为{}的文件记录", fileUploadId);
            return;
        }
        
        if ("已完成".equals(fileUpload.getStatus()) || "格式错误".equals(fileUpload.getStatus())) {
            log.info("文件ID: {}已经处理完成或出错，状态: {}", fileUploadId, fileUpload.getStatus());
            // 查找下一个待处理文件
            processNextFile(fileUpload.getUserId());
            return;
        }

        log.info("更新文件状态为处理中，ID: {}", fileUploadId);
        fileUpload.setStatus("处理中");
        fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
        fileUploadMapper.updateById(fileUpload);
        
        // 处理前再次检查用户余额是否充足
        VipPackage vipPackage = vipPackageMapper.selectOne(new QueryWrapper<VipPackage>()
                .eq("user_id", fileUpload.getUserId()).eq("is_deleted", 0));
        
        if (vipPackage == null || vipPackage.getTotalQuota() - vipPackage.getUsedQuota() < fileUpload.getTotalCount()) {
            log.warn("用户余额不足，无法处理文件ID: {}", fileUploadId);
            fileUpload.setStatus("格式错误");
            fileUpload.setErrorMessage("用户余额不足，无法处理该文件");
            fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
            fileUploadMapper.updateById(fileUpload);
            // 尝试处理下一个文件（可能有更小的文件可以处理）
            processNextFile(fileUpload.getUserId());
            return;
        }

        ExcelReader reader = null; // 声明 reader 以便在 catch 块中访问
        try (InputStream inputStream = fileStorageService.getFileStream(fileUpload.getOriginalFilePath())) {
            String resultPath = resultOutputDir + UUID.randomUUID() + "_" + fileUpload.getFileName();
            Files.createFile(Paths.get(resultPath));
            fileUpload.setResultFilePath(resultPath);
            fileUploadMapper.updateById(fileUpload); // 保存resultFilePath到数据库

            // 重置输入流
            try (InputStream newInputStream = fileStorageService.getFileStream(fileUpload.getOriginalFilePath())) {
                reader = EasyExcel.read(newInputStream, CompanyData.class,
                             new ExcelDataThreadPoolListener(userService, resultPath, fileUploadMapper, vipPackageMapper, fileUploadId))
                        .headRowNumber(2)
                        .build();
                ReadSheet sheet = EasyExcel.readSheet("Sheet1").build();
                reader.read(sheet);
                reader.finish();
            }

            log.info("文件处理完成，ID: {}", fileUploadId);
            // 重新获取fileUpload，因为ExcelDataThreadPoolListener可能已经更新了totalCount
            fileUpload = fileUploadMapper.selectById(fileUploadId);
            fileUpload.setStatus("已完成");
            fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
            fileUploadMapper.updateById(fileUpload);
            
            // 处理完成后，查找并处理下一个文件
            // 使用新方法避免事务问题
            processNextFile(fileUpload.getUserId());
        } catch (Exception e) {
            log.error("处理文件出错，ID: {}", fileUploadId, e);
            fileUpload.setStatus("格式错误");
            fileUpload.setErrorMessage(e.getMessage());
            fileUpload.setUpdateTime(LocalDateTime.now(ZoneId.of("Asia/Shanghai")));
            fileUploadMapper.updateById(fileUpload);

            // 处理失败时，仍然扣减已判断成功的额度
            if (reader != null) {
                ExcelDataThreadPoolListener listener = (ExcelDataThreadPoolListener) reader.getAnalysisContext().getCustom();
                int validPhoneCount = listener.getValidPhoneCount();
                if (validPhoneCount > 0) {
                    if (vipPackage != null) {
                        vipPackage.setUsedQuota(vipPackage.getUsedQuota() + validPhoneCount);
                        vipPackageMapper.updateById(vipPackage);
                    }
                }
            }
            
            // 处理失败后，查找并处理下一个文件
            processNextFile(fileUpload.getUserId());
        }
    }

    /**
     * 非事务方法，确保在事务外处理下一个文件
     * @param userId 用户ID
     */
    public void processNextFile(final Long userId) {
        // 使用新线程避免事务问题
        new Thread(() -> {
            try {
                // 延长等待时间，确保事务完全提交
                Thread.sleep(3000);
                log.info("开始查找用户ID: {}的下一个待处理文件", userId);
                // 查找并处理下一个文件
                findAndProcessNextFile(userId);
            } catch (Exception e) {
                log.error("处理下一个文件时出错", e);
            }
        }).start();
    }
    
    /**
     * 查找并处理用户的下一个待处理文件
     * @param userId 用户ID
     */
    private void findAndProcessNextFile(Long userId) {
        log.info("查找用户ID: {}的下一个待处理文件", userId);
        try {
            // 查询该用户下一个待处理文件
            List<FileUpload> pendingFiles = fileUploadMapper.selectList(
                    new QueryWrapper<FileUpload>()
                            .eq("user_id", userId)
                            .eq("status", "未完成")
                            .eq("is_deleted", 0)
                            .orderByAsc("create_time")
                            .last("limit 1")
            );
            
            log.info("查询到用户ID: {}的待处理文件数量: {}", userId, pendingFiles.size());
            
            if (CollUtil.isNotEmpty(pendingFiles)) {
                FileUpload nextFile = pendingFiles.get(0);
                log.info("找到用户ID: {}的下一个待处理文件，文件ID: {}, 文件名: {}, 状态: {}", 
                        userId, nextFile.getId(), nextFile.getFileName(), nextFile.getStatus());
                
                // 发送到RabbitMQ队列处理
                rabbitTemplate.convertAndSend(RabbitMQConfig.FILE_EXCHANGE, 
                        RabbitMQConfig.FILE_ROUTING_KEY, nextFile.getId());
                log.info("已将下一个文件ID: {}发送到处理队列", nextFile.getId());
            } else {
                log.info("用户ID: {}没有更多待处理文件", userId);
            }
        } catch (Exception e) {
            log.error("查找下一个文件时出错，用户ID: {}", userId, e);
        }
    }

    /**
     * 计算文件中的数据行数（不是号码数量）
     */
    private int calculateDataRowCount(InputStream inputStream) throws IOException {
        AtomicInteger count = new AtomicInteger(0);
        ExcelReader reader = EasyExcel.read(inputStream, CompanyData.class, new AnalysisEventListener<CompanyData>() {
            @Override
            public void invoke(CompanyData data, AnalysisContext context) {
                // 每读取一行数据，计数加1
                count.incrementAndGet();
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {}
        }).headRowNumber(2).build();
        reader.read(EasyExcel.readSheet("Sheet1").build());
        return count.get();
    }

    /**
     * 计算文件中的号码数量（只统计中国手机号码）
     */
    private int calculatePhoneCount(InputStream inputStream) throws IOException {
        AtomicInteger count = new AtomicInteger(0);
        ExcelReader reader = EasyExcel.read(inputStream, CompanyData.class, new AnalysisEventListener<CompanyData>() {
            @Override
            public void invoke(CompanyData data, AnalysisContext context) {
                // 处理主手机号
                int phoneCount = 0;
                String phone = (data.getPhone() == null || data.getPhone().trim().isEmpty()) ? "无" : data.getPhone();
                String[] phones = phone.split("[,，;/\\s]");
                for (String p : phones) {
                    // 只统计中国手机号码
                    if (isValidChineseMobile(p)) {
                        phoneCount++;
                    }
                }

                // 处理额外手机号
                if (data.getMorePhones() != null && !data.getMorePhones().trim().isEmpty()) {
                    String[] morePhones = data.getMorePhones().split("[,，;/\\s]");
                    for (String p : morePhones) {
                        // 只统计中国手机号码
                        if (isValidChineseMobile(p)) {
                            phoneCount++;
                        }
                    }
                }
                count.addAndGet(phoneCount);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {}
        }).headRowNumber(2).build();
        reader.read(EasyExcel.readSheet("Sheet1").build());
        return count.get();
    }
    
    /**
     * 验证是否为中国手机号码
     */
    private boolean isValidChineseMobile(String phone) {
        if (phone == null || phone.trim().isEmpty()) {
            return false;
        }
        
        String trimmedPhone = phone.trim();
        
        // 跳过明显的无效标记
        if (trimmedPhone.equals("-") || trimmedPhone.equals("无") || trimmedPhone.equals("无")) {
            return false;
        }
        
        // 去除非数字字符，保留数字
        String cleanPhone = trimmedPhone.replaceAll("[^\\d]", "");
        
        // 只验证中国手机号：11位，以1开头，第二位是3-9
        return cleanPhone.matches("^1[3-9]\\d{9}$");
    }
    
    /**
     * 验证法定代表人字段
     * 如果非2到4个汉字的数据条数达到总数的10分之一，则拒绝处理文件
     * 
     * @param inputStream 文件输入流
     * @param totalRows 总数据行数
     * @throws IllegalStateException 如果不符合要求的数据条数达到总数的10%
     */
    private void validateLegalRepresentative(InputStream inputStream, int totalRows) throws IOException {
        if (totalRows == 0) {
            return; // 空文件不需要验证
        }
        
        AtomicInteger invalidCount = new AtomicInteger(0);
        
        // 使用EasyExcel读取文件，验证法定代表人字段
        ExcelReader reader = EasyExcel.read(inputStream, CompanyData.class, new AnalysisEventListener<CompanyData>() {
            @Override
            public void invoke(CompanyData data, AnalysisContext context) {
                String representative = data.getRepresentative();
                if (!isValidLegalRepresentative(representative)) {
                    invalidCount.incrementAndGet();
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {}
        }).headRowNumber(2).build();
        
        try {
            reader.read(EasyExcel.readSheet("Sheet1").build());
        } finally {
            reader.finish();
        }
        
        // 计算不符合要求的数据占比
        double invalidRatio = (double) invalidCount.get() / totalRows;
        double threshold = 0.1; // 10%的阈值
        
        log.info("法定代表人字段验证结果: 总行数={}, 不符合要求的行数={}, 占比={:.2f}%, 阈值={:.2f}%", 
                totalRows, invalidCount.get(), invalidRatio * 100, threshold * 100);
        
        if (invalidRatio >= threshold) {
            String errorMessage = String.format(
                "文件中的法定代表人字段不符合要求：共有%d条数据，其中%d条数据的法定代表人不是2到4个汉字，占比%.2f%%，超过了10%%的限制。请修改文件后重新上传。",
                totalRows, invalidCount.get(), invalidRatio * 100
            );
            log.warn(errorMessage);
            throw new IllegalStateException(errorMessage);
        }
    }
    
    /**
     * 验证法定代表人是否为2到4个汉字
     * 
     * @param representative 法定代表人字符串
     * @return true-符合要求（2到4个汉字），false-不符合要求
     */
    private boolean isValidLegalRepresentative(String representative) {
        if (representative == null || representative.trim().isEmpty()) {
            return false; // 空值不符合要求
        }
        
        String trimmed = representative.trim();
        
        // 使用正则表达式验证：2到4个汉字
        // \u4e00-\u9fa5 表示中文字符范围
        Pattern pattern = Pattern.compile("^[\u4e00-\u9fa5]{2,4}$");
        return pattern.matcher(trimmed).matches();
    }
}